import datetime

from models import Base, DateDimension, TimeDimension

from sqlalchemy import Column, Float, String, ForeignKey, Integer

class LocationDimension(Base):
    __tablename__ = "location_dimension"

    id = Column(Integer, primary_key=True)
    latitude = Column(Float)
    longitude = Column(Float)
    city = Column(String)
    state = Column(String(2))


class RideFact(Base):
    __tablename__ = "ride_fact"
    
    id = Column(Integer, primary_key=True)    
    date = Column(ForeignKey('date_dimension.id'))
    time = Column(ForeignKey('time_dimension.id'))
    start = Column(ForeignKey('location_dimension.id'))
    end = Column(ForeignKey('location_dimension.id'))
    distance = Column(Float)


def five_years_past_and_future():
    """Yield dates starting from five years before today and ending
    with five years after today."""

    five_years_ago = datetime.date.today() - datetime.timedelta(days=5*365) #not quite right because of leap years
    five_years_from_now = datetime.date.today() + datetime.timedelta(days=5*365)
    current_date = five_years_ago

    while current_date < five_years_from_now:
        yield current_date
        current_date += datetime.timedelta(days=1)


def every_second():
    """Yield every second in the day starting with midnight."""
    base = datetime.datetime(month=04, day=11, year=1984, hour=0, minute=0, second=0)
    for i in range(86400):
        yield (base + datetime.timedelta(seconds=i)).time()

def populate_date_and_time_dimensions(session):
    for d in five_years_past_and_future():
        dd = DateDimension.from_datetime(d)
        session.add(dd)

    for s in every_second():
        td = TimeDimension.from_datetime(s)
        session.add(td)
    
if __name__ == "__main__":

    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    engine = create_engine('postgresql+psycopg2://staralchemy:0411@127.0.0.1:5432/staralchemy', echo=True)

    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)

    session = Session()

    print("Result: %s" % engine.execute("select 1").scalar())

    populate_date_and_time_dimensions(session)
    
    session.commit()
